package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelo.ControleEstoque;

public class DaoControleEstoque {

    public ArrayList<ControleEstoque> consultarControleEstoque() {
    	
    	ArrayList<ControleEstoque> controle = new ArrayList<ControleEstoque>();
    	
      try {
        Class.forName("org.postgresql.Driver");
            Connection con = null;
             con = DriverManager.getConnection("jdbc:postgresql:Siafu","postgres", "postgresql123");
       if (con != null)
          {
                
         
    	   		  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

                  String query="select utiliza.id_mat, utiliza.qtd from servico, beneficiario, venda, plano, item, tipo_servico, utiliza, material " +
                		  	   "where " +
                		       "servico.id_servico = " + "(SELECT MAX(id_servico) FROM servico)" + " and " +
								"servico.id_benef = beneficiario.id_benef and " +
								"venda.id_venda = beneficiario.id_venda and " +
								"plano.id_plano = venda.id_plano and " +
								"item.id_plano = plano.id_plano and " +
								"tipo_servico.id_tipo_serv = item.id_tipo_serv and " +
								"utiliza.id_tipo_serv = tipo_servico.id_tipo_serv and " +
								"material.id_mat = utiliza.id_mat;";
                		  	   
                  ResultSet rs = stmt.executeQuery(query);
                  
                  while(rs.next()){
                	  ControleEstoque rel = new ControleEstoque();
                	  
                	    int cols = rs.getMetaData().getColumnCount();
                	    Object[] arr = new Object[cols];
                	    for(int i=0; i<cols; i++){
                	      arr[i] = rs.getObject(i+1);
                	      rel.setId_mat(rs.getInt(1));                	      
                	      rel.setQtd(rs.getInt(2));
                	    }
                	    controle.add(rel);
                	}
                  
          }
          else
              System.out.println("Connection Failed!");
      }
          catch(Exception e){
                              e.printStackTrace();
          }
      
    
      
      return controle;
    
    }
    
    
}
